﻿Imports DTO
Imports System.Data.OleDb
Public Class ThuocDAO
    Public Shared Function LayDanhSach() As List(Of ThuocDTO)
        Dim ds As New List(Of ThuocDTO)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection
        cn = Data_Provider.ConnecDTB()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From THUOC"
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            Dim hsDto As New ThuocDTO
            hsDto.MaThuoc = dr("MaThuoc")
            hsDto.TenThuoc = dr("TenThuoc")
            hsDto.CachDung = dr("CachDung")
            hsDto.SoLuong = dr("SoLuong")
            hsDto.DonVi = dr("DonVi")
            ds.Add(hsDto)
        End While
        'B5: Dong ket noi CSDL
        dr.Close()
        cn.Close()
        Return ds
    End Function
    Public Sub CapNhatBang(ByVal dt As DataTable)
        'Kiem tra Business Rule neu co
        Dim svDao As New ThuocDAO()
        svDao.CapNhatBang(dt)
    End Sub
    Public Shared Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Data_Provider.ConnecDTB()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select t.TenThuoc,t.SoLuong "
        strSQL = strSQL & " From THUOC t"
        strSQL = strSQL & " where t.SoLuong >0"
        strSQL = strSQL & " group by  t.TenThuoc,t.SoLuong"
        'B4: Thuc thi chuoi strSQL
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function
    Public Sub Them(ByVal hsDto As ThuocDTO)
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Data_Provider.ConnecDTB()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Insert into THuoc(TenThuoc,  DonVi, SoLuong,CachDung) values (?, ?, ?, ?)"

        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)

        cmd.Parameters.Add("@TenThuoc", OleDbType.WChar)
        cmd.Parameters.Add("@DonVi", OleDbType.Integer)
        cmd.Parameters.Add("@SoLuong", OleDbType.Integer)
        cmd.Parameters.Add("@CachDung", OleDbType.Integer)

        cmd.Parameters("@TenThuoc").Value = hsDto.TenThuoc
        cmd.Parameters("@DonVi").Value = hsDto.DonVi
        cmd.Parameters("@SoLuong").Value = hsDto.SoLuong
        cmd.Parameters("@CachDung").Value = hsDto.CachDung
       

        cmd.ExecuteNonQuery()

        strSQL = "Select @@IDENTITY"
        cmd = New OleDbCommand(strSQL, cn)
        hsDto.MaThuoc = cmd.ExecuteScalar()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub
    Public Shared Function Laytenthuoc() As List(Of ThuocDTO)
        Dim sql As String
        sql = "select * from THUOC "
        Dim conn As OleDbConnection = Data_Provider.ConnecDTB()

        Dim command As OleDbCommand
        command = New OleDbCommand(sql, conn)

        Dim reader As OleDbDataReader = command.ExecuteReader()
        Dim ds_tenthuoc As New List(Of ThuocDTO)

        While reader.Read()
            Dim ts As New ThuocDTO()
            ts.TenThuoc = reader.GetString(1)
            ds_tenthuoc.Add(ts)
        End While

        Return ds_tenthuoc
    End Function
End Class
